﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Reflection;
using IQToolkit.Data.Firebird;
using Sio.Mdm.Entities;
using IQToolkit;
using Sio.Mdm.Types;

namespace Sio.Mdm.BL.Base
{
    public enum SelectType { Data, Count};
    
    public delegate int NextId(string generatorName);

    public class SqlOperations
    {
        public static bool BeleziSpremembe = false;


        /* zaenkrat tega apija ne uporabljamo!
        public static int DeleteEntities<T>(Expression<Func<T, bool>> criteria, Type entityType)
        {
            var eval = new LocalEvaluator();

            var query = criteria != null ? (Expression)Expression.Quote(criteria) : Expression.Constant(null, typeof(Expression<Func<T, bool>>));
            query = PartialEvaluator.Eval(query, eval.CanBeEvaluatedLocally);

            var callMySelf = Expression.Call(
                   null,
                   ((MethodInfo)MethodInfo.GetCurrentMethod()).MakeGenericMethod(typeof(T)),
                    query,
                    Expression.Constant(entityType)
                );

            var oper = new SqlOperations();
            var result = oper.BuildSQLCommand(callMySelf);
            return 0;
        }
         */

        public static QueryCommand BuildSelectBase<T>(Type[] types, MethodCallExpression callMySelf, Type entityType, Session session)
        {
            var oper = new SqlOperations();
            var result = oper.BuildSQLCommand(callMySelf);
            return result;
        }

        public static QueryCommand BuildSelectCommand<T>(SelectType selectType, ISioFilter<T> filter, Type entityType, bool deletedRecords, Session session)
        {
            var eval = new LocalEvaluator();

            //var query = filter != null ? (Expression)Expression.Constant(filter.Expression) : Expression.Constant(null, typeof(Expression<Func<T, bool>>));
            var query = PartialEvaluator.Eval(filter.Expression, eval.CanBeEvaluatedLocally);

            var result = Sio.Mdm.BL.Base.SioMdmFilterFormatter.Format(query, deletedRecords);
            return result;
        }


        public static IEnumerable<QueryCommand> BuildInsertCommand(IEntity entity, Type entityType, NextId GetNextId, Session session)
        {
            Dictionary<string, Object> parameters = new Dictionary<string, object>();
            EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(entityType);

            StringBuilder insert = new StringBuilder();
            List<string> allFields = info.GetInsertableFields();

            insert.AppendFormat("INSERT INTO {0} ", info.DmlTableName);

            List<string> logValues = new List<string>();

            foreach (string field in allFields)
                {
                    string fieldName = (info.FieldMapping.ContainsKey(field.ToLower()) ?
                        info.FieldMapping[field.ToLower()] : field).ToLower();

                    FieldAttribute attr = info.Fields[fieldName];

                    object value = entity.GetField(fieldName);

                if ((attr is PrimaryKeyAttribute) && (((PrimaryKeyAttribute)attr).GeneratorName != null))
                {
                    value = GetNextId(((PrimaryKeyAttribute)attr).GeneratorName);
                    entity.SetField(fieldName, value);
                }

                /*
                if (attrib.UpdateValue != null)
                {
                    paramList.Add(attrib.UpdateValue.ToString());
                    continue;
                }
                 */

                if (value != null)
                {
                    if (value.GetType() == typeof(bool))
                    {
                        Nullable<bool> bv = value as Nullable<bool>;
                        if (bv.Value == true)
                            value = 1;
                        else
                            value = 0;
                    }

                    parameters.Add(field, value);
                    logValues.Add(string.Format("{0}(=> {1})", field, value.ToString()));
                }
            }

            var param1 = parameters.Select(r => r.Key);
            insert.AppendFormat(" \n ({0}) ", String.Join(", ", param1.ToArray()));

            var param2 = parameters.Select(r => String.Format("@{0}", r.Key));

            insert.AppendFormat(" \n VALUES \n   ({0}) ", String.Join(", ", param2.ToArray()));

            var result = new QueryCommand(insert.ToString(), parameters);

            var resultLIst = new List<QueryCommand>();
            resultLIst.Add(result);

            if (BeleziSpremembe)
            {
                Dictionary<string, object> spremembeParameters = new Dictionary<string, object>();

                if (session != null)
                {
                    spremembeParameters.Add("ORGANIZACIJA_ID", session.Organizacija);  
                    spremembeParameters.Add("IDM_STEVILKA", session.IdmStevilka);  
                }
                else
                {
                    spremembeParameters.Add("ORGANIZACIJA_ID", 0);  
                    spremembeParameters.Add("IDM_STEVILKA", 0);  
                }
                spremembeParameters.Add("OPERACIJA", 1); //1 je fiksno in pomeni insert
                spremembeParameters.Add("TABELA", info.TableName);
                spremembeParameters.Add("SPREMEMBE", String.Join(", ", logValues.ToArray()));

                var spremembe = new QueryCommand("INSERT INTO e_zgodovina_sprememb (ORGANIZACIJA_ID, IDM_STEVILKA, OPERACIJA, TABELA, SPREMEMBE) VALUES (@ORGANIZACIJA_ID, @IDM_STEVILKA, @OPERACIJA, @TABELA, @SPREMEMBE)",
                    spremembeParameters);
                resultLIst.Add(spremembe);

            }
            return resultLIst;
        }

        /// <summary>
        /// Kreiram update statement, ki ustreza brisanju preko status fielda.
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="entityType"></param>
        /// <param name="session"></param>
        /// <returns></returns>
        public static IEnumerable<QueryCommand> BuildDeleteByStatusCommand(IEntity entity, Type entityType, Session session)
        {
            var sb = new StringBuilder();

            Dictionary<string, Object> parameters = new Dictionary<string, object>();
            EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(entityType);

            sb.AppendFormat("UPDATE {0} \n ", info.DeletedStatusField.DeclaringTableName);

            sb.AppendFormat("SET {0} = 1 \n ", info.DeletedStatusField.Name);

            //poiščem pravi primary key. Ne morem se zanesti na info.PrimaryKey,
            //ker na monotu deluje drugače kot na .NET-u.
            string primaryKey = info.PrimaryKey.Name;

            foreach (var fld in info.Fields)
            {
                var pk = fld.Value as PrimaryKeyAttribute;
                if (pk != null)
                {
                    if (pk.DeclaringTable == info.DeletedStatusField.DeclaringTableName)
                        primaryKey = pk.Name;
                }
            }

            sb.AppendFormat("WHERE {0} = @{0}", primaryKey);

            parameters.Add(primaryKey, entity.GetField(info.FieldMapping[primaryKey.ToLower()]));

            var result = new QueryCommand(sb.ToString(), parameters);

            var resultLIst = new List<QueryCommand>();
            resultLIst.Add(result);

            if (BeleziSpremembe)
            {
                Dictionary<string, object> spremembeParameters = new Dictionary<string, object>();

                if (session != null)
                {
                    spremembeParameters.Add("ORGANIZACIJA_ID", session.Organizacija);
                    spremembeParameters.Add("IDM_STEVILKA", session.IdmStevilka);
                }
                else
                {
                    spremembeParameters.Add("ORGANIZACIJA_ID", 0);
                    spremembeParameters.Add("IDM_STEVILKA", 0);
                }
                spremembeParameters.Add("OPERACIJA", 4); //4 je fiksno in pomeni deleteByStatus
                spremembeParameters.Add("TABELA", info.TableName);

                var spremembe = new QueryCommand("INSERT INTO e_zgodovina_sprememb (ORGANIZACIJA_ID, IDM_STEVILKA, OPERACIJA, TABELA) VALUES (@ORGANIZACIJA_ID, @IDM_STEVILKA, @OPERACIJA, @TABELA)",
                    spremembeParameters);
                resultLIst.Add(spremembe);
            }
            return resultLIst;
        }
        

        public static IEnumerable<QueryCommand> BuildDeleteCommand(IEntity entity, Type entityType, Session session)
        {
            var sb = new StringBuilder();

            Dictionary<string, Object> parameters = new Dictionary<string,object>();
            EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(entityType);

            sb.AppendFormat("DELETE FROM {0} \n ", info.DmlTableName);

            sb.Append(" WHERE \n");
            int pkCount = 0;
            foreach (string field in info.GetInsertableFields())
                {
                    string fieldName = (info.FieldMapping.ContainsKey(field.ToLower()) ?
                        info.FieldMapping[field.ToLower()] : field).ToLower();

                    FieldAttribute attr = info.Fields[fieldName];

                    object value = entity.GetField(fieldName);

                    if (attr is PrimaryKeyAttribute) 
                    {
                        if (pkCount > 0)
                           sb.Append("  AND ");
                        sb.AppendFormat("{0} = @{0} \n ", field);
                        parameters.Add(field, value);
                        ++pkCount;
                    }
            }
            //parameters.Add(info.PrimaryKey.Name, entity.GetField(info.FieldMapping[info.PrimaryKey.Name.ToLower()]));

            var result = new QueryCommand(sb.ToString(), parameters);

            var resultLIst = new List<QueryCommand>();
            resultLIst.Add(result);

            if (BeleziSpremembe)
            {
                Dictionary<string, object> spremembeParameters = new Dictionary<string, object>();

                if (session != null)
                {
                    spremembeParameters.Add("ORGANIZACIJA_ID", session.Organizacija);
                    spremembeParameters.Add("IDM_STEVILKA", session.IdmStevilka);
                }
                else
                {
                    spremembeParameters.Add("ORGANIZACIJA_ID", 0);
                    spremembeParameters.Add("IDM_STEVILKA", 0);
                }
                spremembeParameters.Add("OPERACIJA", 3); //3 je fiksno in pomeni delete
                spremembeParameters.Add("TABELA", info.TableName);

                var spremembe = new QueryCommand("INSERT INTO e_zgodovina_sprememb (ORGANIZACIJA_ID, IDM_STEVILKA, OPERACIJA, TABELA) VALUES (@ORGANIZACIJA_ID, @IDM_STEVILKA, @OPERACIJA, @TABELA)",
                    spremembeParameters);
                resultLIst.Add(spremembe);
            }
            return resultLIst;
        }

        public static IEnumerable<QueryCommand> BuildUpdateCommand(IEntity entity, Type entityType, Session session)
        {
            if (entity.GetOldValues().Count > 0)
            {
                EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(entityType);
                StringBuilder update = new StringBuilder();

                update.AppendFormat("UPDATE {0} SET ", info.DmlTableName);

                List<string> allFields = info.GetUpdatableFields(entity);
                List<string> paramList = new List<string>();
                Dictionary<string, object> parameters = new Dictionary<string, object>();

                List<string> logValues = new List<string>();

                foreach (string field in allFields)
                {
                    string fieldName = (info.FieldMapping.ContainsKey(field.ToLower()) ?
                        info.FieldMapping[field.ToLower()] : field).ToLower();
                    if (entity.GetOldValues().ContainsKey(fieldName))
                    {
                        FieldAttribute attr = info.Fields[fieldName];

                        if (attr is PrimaryKeyAttribute)
                        {
                            continue;
                        }

                        if (attr.UpdateValue == null)
                        {
                            paramList.Add(String.Format("{0} = @{1}", field, field));
                            var value = entity.GetField(fieldName);
                            if (value.GetType() == typeof(bool))
                            {
                                Nullable<bool> bv = value as Nullable<bool>;
                                if (bv.Value == true)
                                    value = 1;
                                else
                                    value = 0;
                            }

                            parameters.Add(field, value);

                            logValues.Add(string.Format("{0}({1} => {2})", field, entity.GetOldValues()[fieldName], entity.GetField(fieldName)));
                        }


                    }
                }

                update.Append(String.Join(", ", paramList.ToArray()));

                update.Append(" WHERE \n");
                int pkCount = 0;
                foreach (string field in info.GetInsertableFields())
                {
                    string fieldName = (info.FieldMapping.ContainsKey(field.ToLower()) ?
                        info.FieldMapping[field.ToLower()] : field).ToLower();

                    FieldAttribute attr = info.Fields[fieldName];

                    object value = entity.GetField(fieldName);

                    if (attr is PrimaryKeyAttribute)
                    {
                        if (pkCount > 0)
                            update.Append("  AND ");
                        update.AppendFormat("{0} = @{0} \n ", field);
                        parameters.Add(field, value);
                        ++pkCount;
                    }
                }

                //update.AppendFormat(" WHERE {0} = {1}", info.PrimaryKey.Name, entity.GetField(info.FieldMapping.ContainsKey(info.PrimaryKey.Name.ToLower()) ?
                //        info.FieldMapping[info.PrimaryKey.Name.ToLower()] : info.PrimaryKey.Name));

                var cmd = new QueryCommand(update.ToString(), parameters);
                var resultLIst = new List<QueryCommand>();
                resultLIst.Add(cmd);

                if (BeleziSpremembe)
                {
                    Dictionary<string, object> spremembeParameters = new Dictionary<string, object>();

                    if (session != null)
                    {
                        spremembeParameters.Add("ORGANIZACIJA_ID", session.Organizacija);
                        spremembeParameters.Add("IDM_STEVILKA", session.IdmStevilka);
                    }
                    else
                    {
                        spremembeParameters.Add("ORGANIZACIJA_ID", 0);
                        spremembeParameters.Add("IDM_STEVILKA", 0);
                    }
                    spremembeParameters.Add("OPERACIJA", 2); //2 je fiksno in pomeni update
                    spremembeParameters.Add("TABELA", info.TableName);
                    spremembeParameters.Add("SPREMEMBE", String.Join(", ", logValues.ToArray()));

                    var spremembe = new QueryCommand("INSERT INTO e_zgodovina_sprememb (ORGANIZACIJA_ID, IDM_STEVILKA, OPERACIJA, TABELA, SPREMEMBE) VALUES (@ORGANIZACIJA_ID, @IDM_STEVILKA, @OPERACIJA, @TABELA, @SPREMEMBE)",
                        spremembeParameters);
                    resultLIst.Add(spremembe);
                }
                return resultLIst;
            }
            else return null;
        }

        private QueryCommand BuildSQLCommand(Expression query)
        {
            if (query.NodeType == ExpressionType.Lambda)
                query = ((LambdaExpression)query).Body;


            MethodCallExpression mc = query as MethodCallExpression;


            if (mc == null)
            {
                throw new NotSupportedException("Wrong Expression type in SqlOperations.BuildSQL method");
            }

            Dictionary<string, Object> parameters;
            Dictionary<string, Sio.Mdm.Entities.EntityFactory.EntityInfo> aliases;
            String sqlWhere;

            if (mc.Arguments[1].NodeType == ExpressionType.Quote)
            {
                sqlWhere = FirebirdFormatter.MakeWhere(((LambdaExpression)((UnaryExpression)mc.Arguments[1]).Operand).Body, out parameters, out aliases);
            }
            else
                throw new NotSupportedException("Wrong arguments type");

            var from = aliases.Select(r => String.Format("{0} {1}", r.Value.TableName, r.Key)).ToArray();
            string fromTables = String.Join(", ", from);

            var args = mc.Method.GetGenericArguments();

            EntityFactory.EntityInfo info = EntityFactory.GetEntityInfo(args[0]);

            string selectFields = null;
            if ((SelectType)((ConstantExpression)mc.Arguments[0]).Value == SelectType.Data)
            {
                string mainAlias = null;
                foreach (var alias in aliases)
                {
                    if (info.TableName == alias.Value.TableName)
                    {
                        mainAlias = alias.Key;
                        break;
                    }
                }
                if (mainAlias != null)
                    selectFields = String.Format("{0}.*", mainAlias);
                else
                    throw new InvalidOperationException(String.Format("Cannot find alias for table {0}", info.TableName));
            }
            else if ((SelectType)((ConstantExpression)mc.Arguments[0]).Value == SelectType.Count)
            {
                selectFields = " count(*) ";
            }
            else
                throw new InvalidOperationException("Unsupported select type.");

            switch (mc.Method.Name)
            {
                case "BuildSelect":
                    return new QueryCommand(String.Format("SELECT {2} FROM {0} {1}",
                        fromTables, sqlWhere == "" ? "" : String.Format("\n  WHERE {0}", sqlWhere), selectFields), parameters);
                case "DeleteEntities":
                    return new QueryCommand(String.Format("DELETE FROM {0} {1}",
                        info.DmlTableName, sqlWhere == "" ? "" : String.Format("\n  WHERE {0}", sqlWhere)), parameters);
                default:
                    throw new NotSupportedException(String.Format("Wrong method type {0}", mc.Method.Name));
                    ;
            }
        }

    }
}
